import os
import win32com.client

from Config.ReportConfig import *
from Misc.xlDate import *

from datetime import datetime

def extract_trade_from_BOCI(file_path):

	if os.path.isfile(file_path):
		file_name = os.path.split(file_path)[1]
		file_prefix = os.path.splitext(file_name)[0]
		file_suffix = os.path.splitext(file_name)[1]
		
		if file_prefix[:19].lower() == 'springs-trade recap' and  file_suffix[1:].lower() == 'xls':

			xApp = win32com.client.Dispatch("Excel.Application")
			xBook = xApp.Workbooks.Open(file_path)
			xSht = xBook.Worksheets('Sheet1')
			row = xSht.Range("A65536").End(win32com.client.constants.xlUp).Row + 1
			
			trade_rcd = []
			for i in range(4,row):
				trade_date = read_xl_date(float(xSht.Cells(i, 1).Value))
				ticker = xSht.Cells(i, 3).Value
				trade_type = 'BUY' if xSht.Cells(i, 2).Value == 'B' else 'SELL'
				quantity = xSht.Cells(i, 4).Value
				cost_price = xSht.Cells(i, 10).Value/quantity
				curncy = xSht.Cells(i, 11).Value
				settle_date = read_xl_date(float(xSht.Cells(i, 13).Value))
				local_price = xSht.Cells(i, 6).Value
				local_curncy = xSht.Cells(i, 7).Value
				fx = xSht.Cells(i, 12).Value
				commission = xSht.Cells(i, 5).Value
				gross_price = xSht.Cells(i, 8).Value
				tax = xSht.Cells(i, 16).Value
				
				trade_rcd.append([trade_date, ticker, trade_type, quantity, cost_price, curncy, 'PNOTE', settle_date, 
								local_price, local_curncy, fx, commission, None, None, None, tax, 'BOCI', gross_price])
								
			xBook.Close()
			
			#create trade files for ML
			crt_time = datetime.now().strftime('%Y%m%d%H%M%S')
			xBook = xApp.Workbooks.Add()
			xSht = xBook.Worksheets('Sheet1')
			xSht.Cells(1, 1).Value = 'Trade'
			xSht.Cells(1, 2).Value = 1
			xSht.Cells(1, 3).Value = crt_time
			
			for i in range(len(trade_rcd)):
				trd = trade_rcd[i]
				xSht.Cells(2+i, 1).Value = 'XSPRS11'
				xSht.Cells(2+i, 5).Value = 'N'
				xSht.Cells(2+i, 6).Value = 'BY' if trd[2] == 'BUY' else 'SL'
				xSht.Cells(2+i, 7).Value = 'Input'
				xSht.Cells(2+i, 10).Value = 'EQ'
				xSht.Cells(2+i, 11).Value = 'ISIN'
				xSht.Cells(2+i, 12).Value = 'Input'
				xSht.Cells(2+i, 15).Value = 'BOCI'
				xSht.Cells(2+i, 16).Value = trd[0].strftime("%Y%m%d")
				xSht.Cells(2+i, 17).Value = trd[7].strftime("%Y%m%d")
				xSht.Cells(2+i, 19).Value = trd[17]
				xSht.Cells(2+i, 20).Value = 'USD'
				xSht.Cells(2+i, 21).Value = 'USD'
				xSht.Cells(2+i, 23).Value = trd[3]
				xSht.Cells(2+i, 24).Value = trd[11]
				xSht.Cells(2+i, 26).Value = 'A'
				xSht.Cells(2+i, 28).Value = 'TAXES'
				xSht.Cells(2+i, 29).Value = trd[15]
				xSht.Cells(2+i, 39).Value = trd[3]*trd[4]
			
			xSht.Cells(2+len(trade_rcd), 1).Value = 'XSPR'
			xSht.Cells(2+len(trade_rcd), 2).Value = 1
			xSht.Cells(2+len(trade_rcd), 3).Value = len(trade_rcd)
			
			rept_file = REPT_PATH + 'XSPR_EQTradeFile_' + crt_time + '000001T.csv'
			xBook.SaveAs(rept_file, win32com.client.constants.xlCSV)
			xBook.Close()
			del xApp
			
			return trade_rcd
			
		else:
			raise RuntimeError() + 'File is not BOCI confirmation:' + file_name	
	else:
		raise RuntimeError() + 'File does not exist:' + file_path